CREATE TABLE [dbo].[Member_Types]
(
[MEMBER_TYPE] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_MEMBER_TYPE] DEFAULT (''),
[DESCRIPTION] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DESCRIPTION] DEFAULT (''),
[MEMBER_RECORD] [bit] NOT NULL CONSTRAINT [DF_Member_Types_MEMBER_RECORD] DEFAULT ((0)),
[COMPANY_RECORD] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMPANY_RECORD] DEFAULT ((0)),
[DUES_CODE_1] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_1] DEFAULT (''),
[DUES_CODE_2] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_2] DEFAULT (''),
[DUES_CODE_3] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_3] DEFAULT (''),
[DUES_CODE_4] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_4] DEFAULT (''),
[DUES_CODE_5] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_5] DEFAULT (''),
[DUES_CODE_6] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_6] DEFAULT (''),
[DUES_CODE_7] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_7] DEFAULT (''),
[DUES_CODE_8] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_8] DEFAULT (''),
[DUES_CODE_9] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_9] DEFAULT (''),
[DUES_CODE_10] [varchar] (31) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_DUES_CODE_10] DEFAULT (''),
[RATE_1] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_1] DEFAULT ((0)),
[RATE_2] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_2] DEFAULT ((0)),
[RATE_3] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_3] DEFAULT ((0)),
[RATE_4] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_4] DEFAULT ((0)),
[RATE_5] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_5] DEFAULT ((0)),
[RATE_6] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_6] DEFAULT ((0)),
[RATE_7] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_7] DEFAULT ((0)),
[RATE_8] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_8] DEFAULT ((0)),
[RATE_9] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_9] DEFAULT ((0)),
[RATE_10] [numeric] (15, 2) NOT NULL CONSTRAINT [DF_Member_Types_RATE_10] DEFAULT ((0)),
[BILL_COMPANY] [bit] NOT NULL CONSTRAINT [DF_Member_Types_BILL_COMPANY] DEFAULT ((0)),
[SPECIAL_FORM] [varchar] (43) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Member_Types_SPECIAL_FORM] DEFAULT (''),
[COMP_1] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_1] DEFAULT ((0)),
[COMP_2] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_2] DEFAULT ((0)),
[COMP_3] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_3] DEFAULT ((0)),
[COMP_4] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_4] DEFAULT ((0)),
[COMP_5] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_5] DEFAULT ((0)),
[COMP_6] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_6] DEFAULT ((0)),
[COMP_7] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_7] DEFAULT ((0)),
[COMP_8] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_8] DEFAULT ((0)),
[COMP_9] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_9] DEFAULT ((0)),
[COMP_10] [bit] NOT NULL CONSTRAINT [DF_Member_Types_COMP_10] DEFAULT ((0)),
[DEFAULT_SECURITY_GROUP] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIME_STAMP] [timestamp] NULL
) ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_MemberTypes_Delete]
   ON  [dbo].[Member_Types]
   FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @groups TABLE ([GroupKey] uniqueidentifier, [Name] nvarchar(65), [Description] nvarchar(250))
    DECLARE @groupTypeKey uniqueidentifier
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    
    INSERT INTO @groups ([GroupKey], [Name], [Description])
        SELECT COALESCE(gm.[GroupKey], NEWID()), mt.[DESCRIPTION], NULL
          FROM deleted mt LEFT OUTER JOIN [dbo].[GroupMain] gm ON gm.[Name] = mt.[DESCRIPTION] AND gm.[GroupTypeKey] = @groupTypeKey
    DELETE m
      FROM [dbo].[GroupMember] m INNER JOIN @groups g ON m.[GroupKey] = g.[GroupKey]
    DELETE gm
      FROM [dbo].[GroupMain] gm INNER JOIN @groups g ON gm.[GroupKey] = g.[GroupKey]
    DELETE ur
      FROM [dbo].[UniformRegistry] ur INNER JOIN @groups g ON ur.[UniformKey] = g.[GroupKey]
    SET NOCOUNT OFF
END

GO
CREATE TRIGGER [dbo].[asi_MemberTypes_Insert]
   ON  [dbo].[Member_Types]
   FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @now datetime
    DECLARE @userKey uniqueidentifier
    DECLARE @accessKey uniqueidentifier
    DECLARE @groupTypeKey uniqueidentifier
    DECLARE @systemEntityKey uniqueidentifier
    DECLARE @groupComponentKey uniqueidentifier
    DECLARE @groups TABLE ([GroupKey] uniqueidentifier, [Name] nvarchar(65), [Description] nvarchar(250))

    SELECT @now = GETDATE()
    SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'SYSTEM'
    IF @userKey IS NULL SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'MANAGER'
    SELECT @accessKey = [ProtectedAccessKey] FROM [dbo].[AccessArea] WHERE [Name] = 'Everyone Full Control'
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    SELECT @systemEntityKey = [SystemEntityKey] FROM [dbo].[SystemEntity] WHERE [SystemKeyword] = 'Organization'
    SELECT @groupComponentKey = [ComponentKey] FROM [dbo].[ComponentRegistry] WHERE [Name] = 'Group' AND [InterfaceName] = 'BusinessController'

    -- Create a new Member Type Security group for each member type being inserted
    INSERT INTO @groups ([GroupKey], [Name], [Description])
        SELECT COALESCE(gm.[GroupKey], NEWID()), mt.[DESCRIPTION], 'Security Group for ' + mt.[DESCRIPTION] + ' users'
          FROM inserted mt LEFT OUTER JOIN [dbo].[GroupMain] gm ON gm.[Name] = mt.[DESCRIPTION]

    INSERT INTO [dbo].[UniformRegistry] ([UniformKey], [ComponentKey])
        SELECT g.[GroupKey], @groupComponentKey
          FROM @groups g LEFT OUTER JOIN [dbo].[UniformRegistry] ur ON ur.[UniformKey] = g.[GroupKey] AND ur.[ComponentKey] = @groupComponentKey
         WHERE  ur.[UniformKey] IS NULL

    INSERT INTO [dbo].[GroupMain] ([GroupKey], [Name], [Description], [UpdatedByUserKey], [UpdatedOn], [IsSystem], [IsAutoGenerated],
                                   [GroupTypeKey], [Priority], [OwnerAccessKey], [OverrideOwnerGroupKey], [AccessKey],
                                   [CreatedByUserKey], [CreatedOn], [SystemEntityKey],
                                   [IsInvitationOnly], [GroupStatusCode], [IsSimpleGroup], [InheritRolesFlag], [IsSingleRole])
        SELECT g.[GroupKey], g.[Name], g.[Description], @userKey, @now, 1, 1,
               @groupTypeKey, NULL, NULL, NULL, @accessKey, @userKey, @now, @systemEntityKey, 0, 'A', 1, 0, 0
          FROM @groups g
         WHERE NOT EXISTS (SELECT 1 FROM [dbo].[GroupMain] gm WHERE g.[Name] = gm.[Name] AND gm.[GroupTypeKey] = @groupTypeKey)


    -- Populate the new Security Groups
    INSERT INTO [dbo].[GroupMember] ([GroupMemberKey], [GroupKey], [MemberContactKey], [IsActive],
                                     [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
                                     [DropDate], [JoinDate], [MarkedForDeleteOn])
        SELECT NEWID(), g.[GroupKey], cm.[ContactKey], 1, @userKey, @now, @userKey, @now, NULL, NULL, NULL  
          FROM [dbo].[Name] n INNER JOIN inserted mt ON n.[MEMBER_TYPE] = mt.[MEMBER_TYPE]
                              INNER JOIN [dbo].[ContactMain] cm ON n.[ID] = cm.[SyncContactID]
                              INNER JOIN [dbo].[UserMain] um ON um.[UserKey] = cm.[ContactKey]
                              INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION]
         WHERE NOT EXISTS (SELECT 1 FROM [dbo].[GroupMember] gm WHERE gm.[GroupKey] = g.[GroupKey] AND gm.[MemberContactKey] = um.UserKey)
    SET NOCOUNT OFF
END

GO
CREATE TRIGGER [dbo].[asi_MemberTypes_Update]
   ON  [dbo].[Member_Types]
   FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @groupTypeKey uniqueidentifier
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    
    -- Synchronize group name with member type description
    UPDATE g
       SET g.[Name] = i.[DESCRIPTION]
      FROM [dbo].[GroupMain] g INNER JOIN deleted d ON g.[Name] = d.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
                               INNER JOIN inserted i ON d.[MEMBER_TYPE] = i.[MEMBER_TYPE]
     WHERE g.[Name] = d.[DESCRIPTION] AND g.[GroupTypeKey] = @groupTypeKey
     
    SET NOCOUNT OFF
END

GO
ALTER TABLE [dbo].[Member_Types] ADD CONSTRAINT [PK_Member_Types] PRIMARY KEY CLUSTERED ([MEMBER_TYPE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MemberTypes_Description] ON [dbo].[Member_Types] ([DESCRIPTION]) ON [PRIMARY]
GO
GRANT REFERENCES ON  [dbo].[Member_Types] TO [IMIS]
GRANT SELECT ON  [dbo].[Member_Types] TO [IMIS]
GRANT INSERT ON  [dbo].[Member_Types] TO [IMIS]
GRANT DELETE ON  [dbo].[Member_Types] TO [IMIS]
GRANT UPDATE ON  [dbo].[Member_Types] TO [IMIS]
GO